package com.lswms.modules.lswms.dao.stock;

import java.math.BigDecimal;
import java.util.Date;
import java.util.List;
import java.util.Map;

import org.apache.ibatis.annotations.Mapper;
import org.apache.ibatis.annotations.Param;
import org.apache.ibatis.annotations.Select;

import com.baomidou.mybatisplus.core.conditions.Wrapper;
import com.baomidou.mybatisplus.core.conditions.query.QueryWrapper;
import com.baomidou.mybatisplus.core.metadata.IPage;
import com.baomidou.mybatisplus.core.toolkit.Constants;
import com.lswms.common.dao.BaseDao;
import com.lswms.modules.lswms.entity.stock.InventoryEntity;
import com.lswms.modules.lswms.entity.stock.LotEntity;
import org.apache.ibatis.annotations.Update;

/**
 * 属性
 *
 * @author DX sunlightcs@gmail.com
 * @since 1.0.0 2021-06-14
 */
@Mapper
public interface InventoryDao extends BaseDao<InventoryEntity> {
	
	
	@Select(value="select  count(1)  "
			+ "                from  lswms_inventory  inventory, lswms_lot lot , lswms_location  location "
			+ "                where inventory.lot = lot.lot and inventory.location = location.location "
			+ "                        and location.location = #{location} "
			+ "                        and ( "
			+ "                                ( location.ismixedsku = 1 and lot.sku != #{sku} ) "
			+ "                         or ( location.ismixedlot1 = 1 and ((lot.lot1 is null and #{lot1} is null) or lot.lot1 != #{lot1} )) "
			+ "                         or ( location.ismixedlot2 = 1 and ((lot.lot2 is null and #{lot2} is null) or lot.lot2 != #{lot2} )) "
			+ "                         or ( location.ismixedlot3 = 1 and lot.lot3 != #{lot3} ) "
			+ "                         or ( location.ismixedlot4 = 1 and lot.lot4 != #{lot4} ) "
			+ "                         or ( location.ismixedlot5 = 1 and lot.lot5 != #{lot5} ) "
			+ "                         or ( location.ismixedlot6 = 1 and lot.lot6 != #{lot6} ) "
			+ "                         or ( location.ismixedlot7 = 1 and lot.lot7 != #{lot7} ) "
			+ "                         or ( location.ismixedlot8 = 1 and lot.lot8 != #{lot8} ) "
			+ "                         or ( location.ismixedlot9 = 1 and lot.lot9 != #{lot9} ) "
			+ "                         or ( location.ismixedlot10 = 1 and lot.lot10 != #{lot10} ) "
			+ "                         or ( location.ismixedlot11 = 1 and lot.lot11 != #{lot11} ) "
			+ "                         or ( location.ismixedlot12 = 1 and lot.lot12 != #{lot12} ) "
			+ "                         or ( location.ismixedlot13 = 1 and lot.lot13 != #{lot13} ) "
			+ "                         or ( location.ismixedlot14 = 1 and lot.lot14 != #{lot14} ) "
			+ "                         or ( location.ismixedlot15 = 1 and lot.lot15 != #{lot15} ) "
			+ "                        )")
	public Integer canbeismixed(Map<String,Object> map) ;


	@Select(value ="select lswms_inventory.* ,lswms_sku.descr , lswms_sku.factorycdoe factorycdoe,lswms_codelist.listname boxstr ,lswms_sku.isuniquecode, lswms_sku.isconsumable isconsumable,re.lot6 jhdh,re.lot7 chejian,re.lot4 jiage,\r\n"
			+ "lswms_lot.lot1,lswms_lot.lot2,lswms_lot.lot3,re.lot4,lswms_lot.lot5,lswms_lot.lot6,lswms_lot.lot7\r\n"
			+ ",lswms_lot.lot8,lswms_lot.lot9,lswms_lot.lot10,lswms_lot.lot11,lswms_lot.lot12,lswms_lot.lot13,lswms_lot.lot14,lswms_lot.lot15, b.listname lot3str,isnull(c.listname,lswms_lot.lot9) lot9str,d.listname lot10str\r\n"
			+ " from lswms_inventory  left join lswms_sku  on lswms_inventory.sku = lswms_sku.sku\r\n"
			+ "left join lswms_codelist on lswms_inventory.box = lswms_codelist.list and code='BOXLIST' \r\n"
			+ "left join lswms_lot on lswms_inventory.lot = lswms_lot.lot \r\n"
			+ "left join lswms_codelist b on lswms_lot.lot3 = b.list and b.code='YESORNO' \r\n"
			+ "left join lswms_codelist c on lswms_lot.lot9 = c.list and c.code='NEWANDOLD' \r\n"
			+ "left join lswms_receiptdetail re on  lswms_inventory.receiptkey=re.receiptkey and lswms_inventory.sku = re.sku \r\n"
			+ "left join lswms_codelist d on lswms_lot.lot10 = d.list and d.code='YESORNO'   ${ew.customSqlSegment}")
	IPage<InventoryEntity> selectPageBySql(IPage<InventoryEntity> page, @Param(Constants.WRAPPER) Wrapper<InventoryEntity> queryWrapper);

	@Select(value ="select lswms_inventory.* ,lswms_sku.descr , lswms_sku.factorycdoe factorycdoe,lswms_codelist.listname boxstr ,lswms_sku.isuniquecode, lswms_sku.isconsumable isconsumable,\r\n"
			+ "lswms_lot.lot1,lswms_lot.lot2,lswms_lot.lot3,lswms_lot.lot4,lswms_lot.lot5,lswms_lot.lot6,lswms_lot.lot7\r\n"
			+ ",lswms_lot.lot8,lswms_lot.lot9,lswms_lot.lot10,lswms_lot.lot11,lswms_lot.lot12,lswms_lot.lot13,lswms_lot.lot14,lswms_lot.lot15, b.listname lot3str,isnull(c.listname,lswms_lot.lot9) lot9str,d.listname lot10str\r\n"
			+ " from lswms_inventory  left join lswms_sku  on lswms_inventory.sku = lswms_sku.sku\r\n"
			+ "left join lswms_codelist on lswms_inventory.box = lswms_codelist.list and code='BOXLIST' \r\n"
			+ "left join lswms_lot on lswms_inventory.lot = lswms_lot.lot \r\n"
			+ "left join lswms_codelist b on lswms_lot.lot3 = b.list and b.code='YESORNO' \r\n"
			+ "left join lswms_codelist c on lswms_lot.lot9 = c.list and c.code='NEWANDOLD' \r\n"
			+ "left join lswms_codelist d on lswms_lot.lot10 = d.list and d.code='YESORNO'   ${ew.customSqlSegment}")
	public List<InventoryEntity> selectByData(@Param(Constants.WRAPPER)QueryWrapper<InventoryEntity> wrapper);
	
	
	@Select(value ="select * from lswms_inventory a join  lswms_lot b on a.lot = b.lot and a.qty > a.pickqty \r\n"
			+ "	join lswms_location c on a.location = c.location ${ew.customSqlSegment}")
	public List<InventoryEntity> selectAllocation(@Param(Constants.WRAPPER) QueryWrapper<InventoryEntity> inventoryEntity);

	@Select(value ="select * from lswms_inventory a join  lswms_lot b on a.lot = b.lot  ${ew.customSqlSegment}")
	public List<InventoryEntity> selectInventoryAndLot(@Param(Constants.WRAPPER) QueryWrapper<InventoryEntity> inventoryEntity);


	@Select("select count(tray) from lswms_inventory  where tray=#{tray}")
	Integer getTray(@Param("tray")String tray);

	@Select("select count(box) from lswms_inventory  where box=#{box}")
	Integer getBox(@Param("box")String box);

	@Select("select qty from  [dbo].[lswms_inventory] where location=#{toloc} and tray=#{totray}")
	List<BigDecimal> getQtyList(@Param("totray")String totray,@Param("toloc")String toloc);

	@Select(value ="select a.sku,sum(qty) qty from lswms_inventory a join  lswms_lot b on a.lot = b.lot and b.lot6=#{planNo}  group by a.sku")
	List<Map<String,Object>> selectByPlanNo(@Param("planNo")String planNo);

	@Update("update lswms_inventory set lot = #{lot} where id = #{id}")
	void UpdateCargoLot (String lot ,long id);

	@Update("update lswms_lot set lot2 = #{lot2} where lot = #{lot}")
	void UpdateLot2(Date lot2 , String lot);

	@Select("select tray from lswms_inventory where id = #{id}")
	String InventoryTray(long id);

	//找出箱明细的具体信息
	@Select("select * from (" +
			"	select * from lswms_inventory a where a.id in (\n" +
			"		select min(id) from (\n" +
			" 			select * from lswms_inventory where tray in " +
			"			(select cargono from lswms_cargojc) \n" +
			"		) \n" +
			"		b group by(b.tray) having count(tray) >1\n" +
			"	)\n" +
			"	union all \n" +
			"	select * from lswms_inventory a where a.id in (\n" +
			"		select min(id) from (\n" +
			" 			select * from lswms_inventory where tray in " +
			"			(select cargono from lswms_cargojc) \n" +
			"		) \n" +
			"		b group by(b.tray) having count(tray) =1\n" +
			"	)" +
			") c ${ew.customSqlSegment}")
	IPage<InventoryEntity> cargojcSelect(IPage<InventoryEntity> page,@Param(Constants.WRAPPER) QueryWrapper<InventoryEntity> inventoryEntity);

}
